﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Xml.Linq;
using System.IO;
using System.Text;
using System.Diagnostics;

namespace Microsoft.Samples.ServiceHosting.HelloWorld
{
    public partial class Service : System.Web.UI.Page
    {
        SqlConnection myConnection;
        string loc;
        string dist;

        private void Search_Restaurant(string location, string distance)
        {
            if (location == null) location = "0,0";
            string latitude = location.Split(',')[0];
            string longitude = location.Split(',')[1];
            if (distance == null) distance = "0";

            float flat = float.Parse(latitude);
            float flong = float.Parse(longitude);
            float fdistance = float.Parse(distance);
            string maxlat = (flat + fdistance).ToString();
            string minlat = (flat - fdistance).ToString();
            string maxlong = (flong + fdistance).ToString();
            string minlong = (flong - fdistance).ToString();

            SqlCommand myCommand = new SqlCommand("SELECT RESTAURANTNAME,ADDRESS,OCCUPATIONRATE,LATITUDE,LONGITUDE FROM DBO.RESTAURANT WHERE " +
                                                    "LATITUDE > " + minlat + ";", myConnection);
            /*" AND " +
            "LATITUDE < " + maxlat + " AND " +
            "longitude > " + minlong + " AND " +
            "LONGTITUDE < " + maxlong + ";", myConnection);*/
            SqlDataReader myReader = null;
            XElement x = new XElement("RestaurantList");

            x.Add(new XElement("Restaurants"));
            int service_number = 0;
            try
            {
                myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {

                    XElement restaurant = new XElement("Restaurant");
                    restaurant.Add(new XElement("Address", myReader["ADDRESS"].ToString()));
                    restaurant.Add(new XElement("RestaurantName", myReader["RESTAURANTNAME"].ToString()));
                    restaurant.Add(new XElement("OccupationRate", myReader["OCCUPATIONRATE"].ToString()));
                    restaurant.Add(new XElement("Latitude", myReader["LATITUDE"].ToString()));
                    restaurant.Add(new XElement("Longitude", myReader["LONGITUDE"].ToString()));
                    x.Element("Restaurants").Add(restaurant);
                    service_number++;

                    /* Response.Write(myReader["RESTAURANTNAME"].ToString());
                     Response.Write(" ");
                     Response.Write(myReader["ADDRESS"].ToString());
                     Response.Write("<br/>");*/
                }
                myReader.Dispose();
                myCommand.Dispose();
                x.SetAttributeValue("ServiceNumber", service_number);
                StringBuilder sb = new StringBuilder();
                x.Save(new StringWriter(sb));
                Response.Write(sb.ToString());
                
            }
            catch (Exception b)
            {
                Trace.Write(b.ToString());
            }
        }

        private void Delete_Restaurant(string ID)
        {
            SqlCommand myCommand = new SqlCommand("DELETE FROM DBO.RESTAURANT WHERE " +
                                                 "ID=" + ID + ";", myConnection);
            int row_affected = myCommand.ExecuteNonQuery();
            myCommand.Dispose();
            if (row_affected > 0)
            {
                Search_Restaurant(loc,dist);
            }
        }

        private void Update_Restaurant(string ID, string restaurant)
        {
            byte[] byteRestaurant = Encoding.UTF8.GetBytes(restaurant);
            MemoryStream restaurantStream = new MemoryStream(byteRestaurant);
            XElement xmlRestaurant = XElement.Load(restaurantStream);
            /*StringBuilder propertyList = new StringBuilder();
            foreach (XElement curElement in xmlRestaurant.Elements("Property"))
            {
                propertyList.Append((string)(curElement.Element("Name")));
                propertyList.Append(" ");
                propertyList.Append((string)(curElement.Element("Value")));
                propertyList.Append(",");
            }
            propertyList.Remove(propertyList.Length - 1, 1);*/
            string occupationRate = (string)xmlRestaurant.Element("OccupationRate");
            SqlCommand myCommand = new SqlCommand(" UPDATE DBO.RESTAURANT SET OCCUPATIONRATE = "+occupationRate+" WHERE " +
                                                "ID=" + ID + ";", myConnection);
            int row_affected = myCommand.ExecuteNonQuery();
            myCommand.Dispose();
            if (row_affected > 0)
            {
                Search_Restaurant(loc, dist);
            }
        }

        private void Create_Restaurant(string restaurant)
        {
            byte[] byteRestaurant = Encoding.UTF8.GetBytes(restaurant);
            MemoryStream restaurantStream = new MemoryStream(byteRestaurant);
            XElement xmlRestaurant = XElement.Load(restaurantStream);
            string ID = (string)(xmlRestaurant.Element("ID"));
            string Name = (string)(xmlRestaurant.Element("Name"));
            string Address = (string)(xmlRestaurant.Element("Address"));
            string OccupationRate = (string)(xmlRestaurant.Element("OccupationRate"));
            string Latitude = (string)(xmlRestaurant.Element("Latitude"));
            string Longitude = (string)(xmlRestaurant.Element("Longitude"));

            string command = "INSERT INTO DBO.RESTAURANT (ID,RESTAURANTNAME,ADDRESS,OCCUPATIONRATE,LATITUDE,LONGITUDE) VALUES (" + ID + "," +
                                                  "'" + Name + "'" + "," + "'" + Address + "'" + "," + OccupationRate + "," +
                                                   Latitude + "," + Longitude + ");";
            SqlCommand myCommand = new SqlCommand("INSERT INTO DBO.RESTAURANT (ID,RESTAURANTNAME,ADDRESS,OCCUPATIONRATE,LATITUDE,LONGITUDE) VALUES (" + ID + "," +
                                                  "'" + Name + "'" + "," + "'" + Address + "'" + "," +OccupationRate + ","  +
                                                   Latitude+ "," + Longitude + ");", myConnection);

           // try
            //{
            Trace.Write(command);
            Response.Write( command);
            Debug.WriteLine(command);
                int row_affected = myCommand.ExecuteNonQuery();
                myCommand.Dispose();
                if (row_affected > 0)
                {
                    Search_Restaurant(loc,dist);
                }
            /*}
            catch (Exception e)
            {
                Response.Write("?");
                Trace.Write(e.ToString());
            }*/
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            dist= "10";
            myConnection = new SqlConnection("user id=csci5221group05@vyda7dbsvv;" +
                                   "password=network@2011;server=vyda7dbsvv.database.windows.net;" +
                                   "Trusted_Connection=false;" +
                                   "database=mydatabase; " +
                                   "connection timeout=30");
            try
            {
                myConnection.Open();
            }
            catch (Exception a)
            {
                Trace.Write(a.ToString());
            }
            
            loc = Request.QueryString.Get("location");
            dist = Request.QueryString.Get("distance");
            loc = "0,0";
            dist = "100";
            //Search_Restaurant(loc, dist);
           

            string action = Request.Params.Get("action");
            string ID = Request.Params.Get("ID");
            string restaurant = Request.Params.Get("restaurant");
           
            switch (action)
            {
                case "delete":
                    Delete_Restaurant(ID);
                    break;
                case "search":
                    Debug.WriteLine("searching:" + ID);
                    Search_Restaurant(loc,dist);
                    break;
                case "update":
                    Debug.WriteLine("updating:" + ID);
                    Update_Restaurant(ID, restaurant);
                    break;
                case "create":
                    Create_Restaurant(restaurant);
                    break;
                default:
                    break;
            }

    
           // Response.Write(aa + "<br/>");
           // Response.Write(names);
            Response.Write("*");
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
           

            string restaurant = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
                                "<Restaurant>" +
                                "<ID>10000</ID>" +
                                "<Name>aaa</Name>" +
                                "<Address>Minneapolis</Address>" +
                                "<OccupationRate>9</OccupationRate>" +
                                "<Latitude>44</Latitude>" +
                                "<Longitude>44</Longitude>" +
                                "</Restaurant>";
            Create_Restaurant(restaurant);
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            string restaurant2 = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
                               "<Restaurant>" +
                               "<ID>10000</ID>" +
                               "<Name>aaa</Name>" +
                               "<Address>Minneapolis</Address>" +
                               "<OccupationRate>35</OccupationRate>" +
                               "<Latitude>44</Latitude>" +
                               "<Longitude>44</Longitude>" +
                               "</Restaurant>";
            Update_Restaurant("10000", restaurant2);
        }
    }
}